package ch10;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcTest {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/smbms";
            String user = "root";
            String password = "123456";
            Connection conn = DriverManager.getConnection(url,user,password);
            System.out.println(conn.getMetaData());
            //一个静态的(全过程)
            String sql1 = "SELECT *FROM smbms_user WHERE id=1";
            Statement st = conn.createStatement();
            ResultSet rest1 = st.executeQuery(sql1);
            while (rest1.next()){
                int id1 = rest1.getInt("id");
                String userCode1 = rest1.getString("userCode");
                String userName1 = rest1.getString("userName");
                System.out.println("id="+id1+"userCode="+userCode1+"userName="+userName1);
            }
            //一个动态的
            List<UserBean> userBeans = new ArrayList<>();
            String sql2 ="SELECT * FROM smbms_user";
            PreparedStatement prep = conn.prepareStatement(sql2);
//            prep.setInt(1,10);
            ResultSet rst2 = prep.executeQuery();

            while (rst2.next()){
                UserBean userBean = new UserBean();
                userBean.setId(rst2.getInt("id"));
                userBean.setUserCode(rst2.getString("userCode"));
                userBean.setUserName(rst2.getString("userName"));
                userBean.setUserPassword(rst2.getString("userPassword"));
                userBean.setGender(rst2.getInt("gender"));
                userBean.setBirthday(rst2.getDate("birthday"));
                userBean.setPhone(rst2.getString("phone"));
                userBean.setAddress(rst2.getString("address"));
                userBean.setUserRole(rst2.getInt("userRole"));
                userBeans.add(userBean);
            }

            //两个静态的关闭服务
//            rest1.close();
//            st.close();
            //释放两个动态的
            for (UserBean userBean : userBeans) {
                System.out.println(userBean);
            }
            rst2.close();
            prep.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();

        }
    }
}
